import xlrd,xlwt,os
def main(a,b,c,d,e,path):
    workbook = xlrd.open_workbook("./"+path)
    booksheet = workbook.sheet_by_index(0)         #用索引取第一个sheet
    # booksheet = workbook.sheet_by_name('Sheet 1')  #或用名称取sheet
    #读单元格数据
    # cell_11 = booksheet.cell_value(0,0)
    # print(cell_11)
    # cell_21 = booksheet.cell_value(1,0)
    #读一行数据
    #总行数
    # print(booksheet.nrows)
    #总列数
    # print(booksheet.ncols)
    all_result={}
    key_words={'fam':388,'hex':521,'rox':526,'cy5':1388}
    for i in range(1,booksheet.nrows):
        number=[]
        number.append(booksheet.cell(i,a).value)
        number.append(booksheet.cell(i,b).value)
        number.append(booksheet.cell(i,c).value)
        number.append(booksheet.cell(i,d).value)
        number.append(booksheet.cell(i,e).value)
        ccc={'/':-100,'':-100}
        number=[ccc[i] if i in ccc else i for i in number]
        if -1.5<=number[1]<=1.5 or -1.5<=number[2]<=1.5:
            if -1.5<=number[3]<=1.5 or -1.5<=number[4]<=1.5:
                temp=[]
                temp.append(booksheet.cell(i,4).value)
                temp.append(str(key_words[str(booksheet.cell(i,4).value)])+'杂合突变')
                all_result[i]=temp
            else:
                temp=[]
                temp.append(booksheet.cell(i,4).value)
                temp.append(str(key_words[str(booksheet.cell(i,4).value)])+'纯合突变')
                all_result[i]=temp
        elif  abs(number[1])>1.5 and abs(number[2])>1.5 and -1.5<=number[3]<=1.5 or -1.5<=number[4]<=1.5:
            temp=[]
            temp.append(booksheet.cell(i,4).value)
            temp.append(str(key_words[str(booksheet.cell(i,4).value)])+'野生')
            all_result[i]=temp
        
    all_result=sorted(all_result.items(), key=lambda item:item[0])
    # print(all_result)
    # row_3 = booksheet.row_values(0)
    # print(row_3)
    # col_value = booksheet.col_values(7)
    # print(col_value)
    newWorkbook = xlwt.Workbook()
    newWorksheet = newWorkbook.add_sheet(workbook.sheet_names()[0])
    newWorksheet.write(0,0,'fam')
    newWorksheet.write(0,1,'hex')
    newWorksheet.write(0,2,'rox')
    newWorksheet.write(0,3,'cy5')
    for i in all_result:
        try:
            if i[1][0] == 'fam':
                newWorksheet.write(i[0],0,i[1][1])
            elif i[1][0] == 'hex':
                newWorksheet.write(i[0],1,i[1][1])
            elif i[1][0] == 'rox':
                newWorksheet.write(i[0],2,i[1][1])
            elif i[1][0] == 'cy5':
                newWorksheet.write(i[0],3,i[1][1])
        except:
            pass
    newWorkbook.save('excelwrite.xls')
    try:
        os.startfile('excelwrite.xls')
    except:
        pass
    
def GetImgNameByEveryDir(file_dir,videoProperty):  
    # Input   Root Dir and get all img in per Dir.
    # Out     Every img with its filename and its dir and its path  
    FileNameWithPath = [] 
    FileName         = []
    FileDir          = []
    for root, dirs, files in os.walk(file_dir):  
        for file in files:  
            if os.path.splitext(file)[1] in videoProperty:  
                FileNameWithPath.append(os.path.join(root, file))  # 保存图片路径
                FileName.append(file)                              # 保存图片名称
                FileDir.append(root[len(file_dir):])               # 保存图片所在文件夹
    return FileName,FileNameWithPath,FileDir


while True:
    print('请输入文件与行列（从0开始数）,文件所在地输入为100退出应用。')
    FileName,FileNameWithPath,FileDir=GetImgNameByEveryDir('.','.xls')
    for i in range(len(FileName)):
        print(str(i)+" : "+FileNameWithPath[i])
    path=input('文件所在序号：')
    if path=='100':
        break
    path=FileName[int(path)]
    a=int(input('染料所在列：'))
    b=int(input('Tm1突变所在列：'))
    c=int(input('Tm2突变所在列：'))
    d=int(input('Tm1野生所在列：'))
    e=int(input('Tm2野生所在列：'))
    try:
        main(a,b,c,d,e,path)
    except:
        print('输入的行列不对，请重新输入！')
